var express = require('express')
var router = express.Router()
const tools = require('../routes/upload')
// 导入db, 操作数据库
const { getAll, getById, exec } = require('../db/index')

router.get('/all', async (req, res) => {
  // 操作数据库
  let sql = `select * from article`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取所有博客成功',
    result: {
      data: data
    }
  })
})
router.get('/newblog', async (req, res) => {
  // 操作数据库
  let sql = `select * from article ORDER BY create_time DESC limit 9`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取最新博客成功',
    result: {
      data: data
    }
  })
})
router.get('/correlate/:categoryid', async (req, res) => {
  // 操作数据库
  const categoryid = req.params.categoryid
  let sql = `select * from article where category_id = '${categoryid}' limit 4`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取相关分类博客成功',
    result: {
      data: data
    }
  })
})
router.get('/someblog', async (req, res) => {
  // 操作数据库
  let sql = ` SELECT * FROM article ORDER BY RAND() LIMIT 5`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取随机博客成功',
    result: {
      data: data
    }
  })
})
router.get('/oneblog', async (req, res) => {
  // 操作数据库
  const { category } = req.query
  if(category==undefined){
    let sql = ` SELECT * FROM article ORDER BY RAND() LIMIT 1`
    const data = await getAll(sql)
    res.send({
      code: 0,
      message: '获取随机博客成功',
      result: {
        data: data
      }
    })
  }else{
    let sql = `SELECT * from article WHERE category_id = (SELECT id from categories WHERE alias = '${category}') ORDER BY RAND() LIMIT 1`
    const data = await getAll(sql)
    res.send({
      code: 0,
      message: '获取随机博客成功',
      result: {
        data: data
      }
    })
  }
})
router.get('/', async (req, res) => {
  // 操作数据库
  var { page, size } = req.query
  var sql = `select count(*) as total from article`
  const obj = await getById(sql)
  var offset = (page - 1) * size
  sql = `select * from article limit ${offset},${size}`
  var data = await getAll(sql)
  if (data.length == 0) {
    page = page - 1
    offset = (page - 1) * size
    sql = `select * from article limit ${offset},${size}`
    data = await getAll(sql)
  }
  res.send({
    code: 0,
    message: '获取博客成功',
    result: {
      total: obj.total,
      data: data
    },
    page: page
  })
})
router.get('/:id', async (req, res) => {
  // 操作数据库
  const id = req.params.id
  let sql = `select * from article where id = '${id}'`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '获取博客成功',
    result: data
  })
})

router.get('/search/blog', async (req, res) => {
  // 操作数据库
  const { page, size, searchMsg } = req.query
  let offset = (page - 1) * size
  let sql = `SELECT * from article WHERE tittle LIKE '%${searchMsg}%' limit ${offset},${size}`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '查询博客成功',
    result: {
      data: data
    }
  })
})
router.get('/search/category', async (req, res) => {
  // 操作数据库
  const { page, size, searchMsg } = req.query
  let offset = (page - 1) * size
  let sql = `SELECT * from article WHERE category_id = (SELECT id from categories WHERE alias = '${searchMsg}') limit ${offset},${size}`
  const data = await getAll(sql)
  res.send({
    code: 0,
    message: '查询博客成功',
    result: {
      data: data
    }
  })
})
router.post('/', async (req, res) => {
  const { tittle, author, category_id, abstract, content, image_address } = req.body
  let sql = `insert into article (tittle,author,category_id,abstract,content,image_address,views) values ('${tittle}','${author}','${category_id}','${abstract}','${content}','${image_address}',1)`
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '添加博客成功',
    data: data
  })
})
router.delete('/:id', async (req, res) => {
  const id = req.params.id
  let sql = `delete from article where id ='${id}' `
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '删除博客成功',
    data: data
  })
})
router.delete('/', async (req, res) => {
  const { selectBlogId } = req.body
  let sql = `delete from article where id in (${selectBlogId})`
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '删除博客成功',
    data: data
  })
})
router.put('/:id', async (req, res) => {
  const id = req.params.id
  const { tittle, author, category_id, abstract, content, image_address } = req.body
  let sql = `update article set tittle='${tittle}',author='${author}',category_id='${category_id}',abstract='${abstract}',content='${content}',image_address='${image_address}',updated_time=now() where id = ${id}`
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '修改博客成功',
    data: data
  })
})
router.put('/view/:id', async (req, res) => {
  const id = req.params.id
  let { views } = req.body
  views = views + 1
  let sql = `update article set views='${views}' where id = ${id}`
  const data = await exec(sql)
  res.send({
    code: 0,
    message: '修改阅读量成功',
    data: data
  })
})
router.post('/upload', tools.upload().single('file'), (req, res) => {
  //single为multer对象的一个api
  res.send({
    // 给前端返回的数据
    file: req.file,
    path: `http://localhost:3000/images/${req.file.filename}` //返回url地址
  })
})
module.exports = router
